--- CS-005-09

use medata

-- 1.- Eliminar las FK existententes y Indices Existentes

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__T005__T011NoClie__145C0A3F]') AND parent_object_id = OBJECT_ID(N'[dbo].[T005]'))
ALTER TABLE [dbo].[T005] DROP CONSTRAINT [FK__T005__T011NoClie__145C0A3F]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__T013__T011NoClie__72C60C4A]') AND parent_object_id = OBJECT_ID(N'[dbo].[T013]'))
ALTER TABLE [dbo].[T013] DROP CONSTRAINT [FK__T013__T011NoClie__72C60C4A]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__T025__T011NoClie__7D439ABD]') AND parent_object_id = OBJECT_ID(N'[dbo].[T025]'))
ALTER TABLE [dbo].[T025] DROP CONSTRAINT [FK__T025__T011NoClie__7D439ABD]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T025]') AND name = N'Relation_1')
DROP INDEX [Relation_1] ON [dbo].[T025] WITH ( ONLINE = OFF )
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__T026__T011NoClie__00200768]') AND parent_object_id = OBJECT_ID(N'[dbo].[T026]'))
ALTER TABLE [dbo].[T026] DROP CONSTRAINT [FK__T026__T011NoClie__00200768]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T026]') AND name = N'Relation_1')
DROP INDEX [Relation_1] ON [dbo].[T026] WITH ( ONLINE = OFF )
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__T026__T011NoClie__47DBAE45]') AND parent_object_id = OBJECT_ID(N'[dbo].[T026]'))
ALTER TABLE [dbo].[T026] DROP CONSTRAINT [FK__T026__T011NoClie__47DBAE45]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__T025__T011NoClie__44FF419A]') AND parent_object_id = OBJECT_ID(N'[dbo].[T025]'))
ALTER TABLE [dbo].[T025] DROP CONSTRAINT [FK__T025__T011NoClie__44FF419A]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__T013__T011NoClie__2B3F6F97]') AND parent_object_id = OBJECT_ID(N'[dbo].[T013]'))
ALTER TABLE [dbo].[T013] DROP CONSTRAINT [FK__T013__T011NoClie__2B3F6F97]
GO

--1.1- Eliminar la definicion de PK de la Tabla T011
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[T011]') AND name = N'PK__T011__060DEAE8')
ALTER TABLE [dbo].[T011] DROP CONSTRAINT [PK__T011__060DEAE8]
GO

--2.- Renombar la Columna T011NoCliente

SP_RENAME 'T011.[T011NoCliente]' , 'T011Entity', 'COLUMN'

--3.- Crear la definicion de PK de la Tabla T011

ALTER TABLE [dbo].[T011] ADD 
	PRIMARY KEY CLUSTERED ([T011Entity])
GO

--4.- Renombar la columna T011NoCliente en la Tabla T005

SP_RENAME 'T005.[T011NoCliente]' , 'T011Entity', 'COLUMN'

--5.- Renombar la columna T011NoCliente en la Tabla T013

SP_RENAME 'T013.[T011NoCliente]' , 'T011Entity', 'COLUMN'

--6.- Renombar la columna T011NoCliente en la Tabla T025

SP_RENAME 'T025.[T011NoCliente]' , 'T011Entity', 'COLUMN'

--7.- Renombar la columna T011NoCliente en la Tabla T026

SP_RENAME 'T026.[T011NoCliente]' , 'T011Entity', 'COLUMN'

--8.- Crear de nuevo las relaciones entre tablas 

ALTER TABLE [dbo].[T005] ADD 
	FOREIGN KEY ([T011Entity])
		REFERENCES [dbo].[T011]([T011Entity])
		ON DELETE NO ACTION
		ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[T013] ADD 
	FOREIGN KEY ([T011Entity])
		REFERENCES [dbo].[T011]([T011Entity])
GO

ALTER TABLE [dbo].[T025] ADD 
	FOREIGN KEY ([T011Entity])
		REFERENCES [dbo].[T011]([T011Entity])
GO

CREATE INDEX [Relation_1] ON [dbo].[T025]
(
	[T011Entity]
)
GO


ALTER TABLE [dbo].[T026] ADD 
	FOREIGN KEY ([T011Entity])
		REFERENCES [dbo].[T011]([T011Entity])
GO

CREATE INDEX [Relation_1] ON [dbo].[T026]
(
	[T011Entity]
)
GO


--9.- Registar el cambio en la tabla de actualizaciones

DECLARE @T027IdScript as varchar(80)
DECLARE @T027Descripcion as varchar(360)
SET @T027IdScript='CS-005-09'
SET @T027Descripcion='Se renombra columna T011NoCliente a T011Entity en Tablas T011,T013,T025,T026'
INSERT INTO T027(T027IdScript,T027Descripcion,T027FechaRealizado) VALUES (@T027IdScript,@T027Descripcion,getdate())